package com.fcscs.ecms.dao.hibernate;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.fcscs.core.dao.ConnectionManager;
import com.fcscs.core.domain.BaseEntity.DBLoc;
import com.fcscs.cservice.domain.MainMenuItem;
import com.fcscs.cservice.domain.model.MainMenuItemView;
import com.fcscs.cservice.ws.WebServiceUtils;
import com.fcscs.cservice.ws.schema.common.MenuListView;
import com.fcscs.cservice.ws.schema.common.PictureFrameView;
import com.fcscs.cservice.ws.schema.common.PictureView;
import com.fcscs.ecms.dao.ECMSMainMenuDao;

@SuppressWarnings("unchecked")
@Repository("mainMenuDao")
public class ECMSMainMenuDaoHibernate implements ECMSMainMenuDao {

	
	private static DBLoc TBLoc = new MainMenuItem().getDBLoc();

	@Autowired
	private ConnectionManager connMgr;
	
	private static final String find_mainMenu_list = "FROM MainMenuItem mm where mm.orgId=:orgId and mm.propertyId=:propId ";
	
	private static final String MAINMENUITEM_NAME_CNT  = "select count(*) from main_menu_item where org_id = :orgId and " +
	" property_id = :propId and menu_type = :name and id <> :id and active = true ";
	
	private long orgId;
	
	@Override
	public MainMenuItem getMainMenuItem(long id) {
		return (MainMenuItem) connMgr.getCurrentSession(TBLoc).get(MainMenuItem.class, id);
	}

	@Override
	public List<MainMenuItem> getMainMenuItemList(long orgId, long propId,boolean isCurrent) {

		Query q = connMgr.getCurrentSession(TBLoc).createQuery(find_mainMenu_list);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		
		return q.list();
	}

	@Override
	public List<MainMenuItem> getMainMenuItemList(long orgId, long propId,long versionId) {
		Query q = connMgr.getCurrentSession(TBLoc).createQuery(find_mainMenu_list);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		
		return q.list();
	}

	@Override
	public List<MainMenuItem> saveMainMenuItemList(List<MainMenuItem> mmiList) {
		
		Session session  = connMgr.getCurrentSession(TBLoc);
		for(MainMenuItem mmi : mmiList){
			session.saveOrUpdate(mmi);
		}
		return mmiList;
	}

	@Override
	public MainMenuItem createMainMenuItem(MainMenuItem mmi) {
			connMgr.getCurrentSession(TBLoc).save(mmi);
		return mmi;
	}

	@Override
	public MainMenuItem saveMainMenuItem(MainMenuItem mmi) {
		connMgr.getCurrentSession(TBLoc).saveOrUpdate(mmi);
		return mmi;
	}

	@Override
	public boolean deleteMainMenuItem(MainMenuItem mmi) {
		String sql = "Update MainMenuItem sd set sd.active = false where sd.id=:id";
		Query q = connMgr.getCurrentSession(TBLoc).createQuery(sql);
		q.setLong("id", mmi.getId());
		
		return (q.executeUpdate()== 1);
	}

	@Override
	public boolean permDeleteMainMenuItem(MainMenuItem mmi) {
		try {
			connMgr.getCurrentSession(TBLoc).delete(mmi);
			return true;
		} catch (Exception e) {
			return false;
		}
		
	}

	@Override
	public boolean isMainMenuItemTaken(long orgId, long propId,String menuType, Long id, long versionId) {
		
		Query q  = connMgr.getCurrentSession(TBLoc).createSQLQuery(MAINMENUITEM_NAME_CNT);
		q.setString("name", menuType);
		q.setLong("id", id == null? -1 : id);
		q.setLong("orgId", orgId);
		q.setParameter("propId", propId, Hibernate.LONG);
		
		return ((Number) q.uniqueResult()).intValue() > 0;
	}

	@Override
	public List<MainMenuItemView> getMainMenuItemViewList(long orgId,long propId) {
	
		String sql = "select mm.id, mm.menuType, mm.menuName, mm.menuNameSecLang, mm.menuNameThdLang, " +
				"mm.menuRename, mm.menuRenameSecLang, mm.menuRenameThirdSecLang, mm.active, i.image " +
				"from MainMenuItem mm ,ImageResource i where mm.largeImageId=i.id and mm.orgId=:orgId and mm.propertyId=:propId";
		
		Query sqlQu = connMgr.getCurrentSession(TBLoc).createQuery(sql);
		sqlQu.setParameter("orgId", orgId);
		sqlQu.setParameter("propId", orgId);
		
		List<MainMenuItemView> list = new ArrayList<MainMenuItemView>();
		
		List<Object[]> categories = (List<Object[]>)sqlQu.list();
		for(Object[] o : categories) {
			MainMenuItemView mmiv = new MainMenuItemView();
			mmiv.setMainMenuItemId((Long) o[0]);
			mmiv.setType((String) o[1]);
			mmiv.setLabel((String) o[2]);
			mmiv.setLabelSecLang((String) o[3]);
			mmiv.setLabelThirdSecLang((String) o[4]);
			mmiv.setLabelRename((String) o[5]);
			mmiv.setLabelRenameSecLang((String) o[6]);
			mmiv.setLabelRenameThirdSecLang((String) o[7]);
			mmiv.setActivate((Boolean) o[8]);
			mmiv.setImageFile(WebServiceUtils.convertToString((byte[])o[9]));
			list.add(mmiv);
		 }
		return list;
	}

	/**
	 * getPictureFrameViews method for all Services
	 * @param orgId , propId , frameId
	 * @return pictureFrameView 
	 */
	@Override
	public PictureFrameView getPictureFrameViews(long orgid, long propId ,long id) {
		// TODO Auto-generated method stub
		System.out.println(id+" id ");
//		String sql = "select pf.id , pf.version ,pf.version,pf.period , pf.proprietaryId , " +
//				"pf.pictureId , pf.pictureVersion " +
//				"from PictureFrameVId pf, where pf.proprietaryId =:propId , pf.id=:id";
//	String sql = 
//			" Select ecms_hotel_staff_v03.global_picture_frame.id, " +
//			" ecms_hotel_staff_v03.global_picture_frame.version, ecms_hotel_staff_v03.global_picture_frame.period, "+
//			//"  ecms_hotel_staff_v03.global_picture.version as 'pictureVersion', " +
//			" ecms_hotel_staff_v03.global_picture.binaryData "+
//			"from ecms_hotel_staff_v03.global_picture_frame_elmts  inner join ecms_hotel_staff_v03.global_picture_frame "+
//			"on ecms_hotel_staff_v03.global_picture_frame.id = ecms_hotel_staff_v03.global_picture_frame_elmts.frameId "+ 
//			"inner join ecms_hotel_staff_v03.global_picture "+
//			"on ecms_hotel_staff_v03.global_picture_frame_elmts.frameId  = ecms_hotel_staff_v03.global_picture.id ";	
//		String sql = "Select * from ecms_hotel_staff_v03.global_picture_frame";
//		String sql = "Select ecms_hotel_staff_v03.global_picture_frame.id as'frameId' ," +
//"ecms_hotel_staff_v03.global_picture_frame.version as 'frameVersion', "+ 
//"ecms_hotel_staff_v03.global_picture_frame.period , "+
//"ecms_hotel_staff_v03.global_picture_frame.proprietaryId , "+ 
//"ecms_hotel_staff_v03.global_picture.id as 'pictureId', " +
//"ecms_hotel_staff_v03.global_picture.version as 'pictureVersion', "+ 
//"ecms_hotel_staff_v03.global_picture.binaryData "+
//"from ecms_hotel_staff_v03.global_picture_frame_elmts "+  
//"inner join ecms_hotel_staff_v03.global_picture_frame "+
//"on ecms_hotel_staff_v03.global_picture_frame.id = ecms_hotel_staff_v03.global_picture_frame_elmts.frameId "+ 
//"inner join ecms_hotel_staff_v03.global_picture "+
//"on ecms_hotel_staff_v03.global_picture_frame_elmts.frameId  = ecms_hotel_staff_v03.global_picture.id  "; 
		String sql = "SELECT * FROM ecms_hotel_staff_v03.picture_frame_v where proprietaryId ='"+propId+"' and frameId = '"+id+"'";
		Query sqlQu = connMgr.getCurrentSession(TBLoc).createSQLQuery(sql)
				.addScalar("frameId",Hibernate.INTEGER)
				.addScalar("frameVersion",Hibernate.INTEGER)
				.addScalar("period",Hibernate.INTEGER)
				.addScalar("proprietaryId",Hibernate.INTEGER)
				.addScalar("pictureId",Hibernate.INTEGER)
				.addScalar("pictureVersion",Hibernate.INTEGER)
				.addScalar("binaryData",Hibernate.BLOB);
		
//		sqlQu.setParameter("orgId", orgId);
//		sqlQu.setParameter("propId", propId);
//		sqlQu.setParameter("id", id);
		
//		PictureFrameView pictureFrameView = new PictureFrameView();
		List<Object[]> objects = (List<Object[]>)sqlQu.list();
		System.out.println(objects.size()+" ");
		PictureFrameView pictureFrameView = new PictureFrameView();
		pictureFrameView.setId(Long.parseLong(objects.get(0)[0].toString()));
		pictureFrameView.setPeriod(Long.parseLong(objects.get(0)[2].toString()));
		pictureFrameView.setVersion(Long.parseLong(objects.get(0)[1].toString()));
		List<PictureView> lisPictureViews = new ArrayList<PictureView>();
		for(Object[] objTmp : objects){
			PictureView pictureView = new PictureView();
			pictureView.setId(Long.parseLong(objTmp[4].toString()));
			pictureView.setVersion(Long.parseLong(objTmp[5].toString()));
			
		lisPictureViews.add(pictureView);	
		}
		pictureFrameView.setPictureViewList(lisPictureViews);
		
	
		return pictureFrameView;
	}

	/**
	 * getMenuListViews method for MainMenuService
	 * @param orgId , propId , languageId 
	 * @return list of MenuListView 
	 */	
	@Override
	public List<MenuListView> getMenuListViews(long orgId, long propId, String langId) {
		// TODO Auto-generated method stub
		String sql = 
				"SELECT  ecms_hotel_staff_v03.global_module_info.typeId," +
				"ecms_hotel_staff_v03.global_module_info.id, " +
				"ecms_hotel_staff_v03.global_module_info.menuPictureRef," +
				"(select ecms_hotel_staff_v03.global_text.textUnicodeValue from ecms_hotel_staff_v03.global_text " +
					"where languageId = '"+langId+"' and id =ecms_hotel_staff_v03.global_module_info.titleRef) as 'Title'," +
				"(select ecms_hotel_staff_v03.global_text.textUnicodeValue from ecms_hotel_staff_v03.global_text " +
					"where languageId = '"+langId+"' and id =ecms_hotel_staff_v03.global_module_info.subTitleRef) as 'SubTitle'," +
				"(select ecms_hotel_staff_v03.global_picture.version from ecms_hotel_staff_v03.global_picture " +
					"where id =ecms_hotel_staff_v03.global_module_info.menuPictureRef) as 'menuPicturerefVersion'" +
					", ecms_hotel_staff_v03.mdl_main_screen.pictureFrameRef ,"+
					" global_module_info.isScheduleScreen  "+
					"FROM ecms_hotel_staff_v03.mdl_main_screen_menu_list " +
						" inner join ecms_hotel_staff_v03.mdl_main_screen on mdl_main_screen.id = ecms_hotel_staff_v03.mdl_main_screen_menu_list.mainScreenId" +
						" inner join ecms_hotel_staff_v03.global_module_info on ecms_hotel_staff_v03.mdl_main_screen_menu_list.moduleId = ecms_hotel_staff_v03.global_module_info.id" +
						" where ecms_hotel_staff_v03.mdl_main_screen.proprietaryId = "+propId+" and ecms_hotel_staff_v03.mdl_main_screen_menu_list.isActive = true " +
								" and ecms_hotel_staff_v03.mdl_main_screen.type =1 " +
						"order by  ecms_hotel_staff_v03.mdl_main_screen_menu_list.displayOrder";
		Query sqlQu = connMgr.getCurrentSession(TBLoc).createSQLQuery(sql);
//		sqlQu.setParameter("orgId", orgId);
//		sqlQu.setParameter("propId", orgId);
//		sqlQu.setParameter("languageID", langId);
		
		int id = 1 ;
		List<MenuListView> lisMenuListViews = new ArrayList<MenuListView>();
		List<Object[]> objects = (List<Object[]>)sqlQu.list();
		System.out.println(objects.size());
		for(Object[] objTmp : objects){
			MenuListView menuListView = new MenuListView();
			for(int i =0 ; i<objTmp.length ; i++){
				
				System.out.println(String.valueOf(objTmp[i]));
				
			}
			if(!Boolean.parseBoolean(objTmp[7].toString())){
				
			
			menuListView.setId(Long.parseLong(String.valueOf(objTmp[1])));
			menuListView.setSubtitle(String.valueOf(objTmp[4]));
			menuListView.setTitle(String.valueOf(objTmp[3]));
			menuListView.setTypeId(String.valueOf(objTmp[0]));
			PictureView pictureView = new PictureView();
			pictureView.setId(Long.parseLong(objTmp[2].toString()));
			pictureView.setVersion(Long.parseLong(objTmp[5].toString()));
			menuListView.setPictureView(pictureView);
			lisMenuListViews.add(menuListView);
			System.out.println("not scheduled");
			}		
			else {
				
				System.out.println("scheduled "+ objTmp[0].toString() + " " +objTmp[1].toString() );
				Calendar currentTime = Calendar.getInstance();
				System.out.println(currentTime.getTime());
				String sqlSchedule = "SELECT activationDate , activationTime , isActivated " +
						"FROM ecms_hotel_staff_v03.schedule_activation	" +
						"where ecms_hotel_staff_v03.schedule_activation.moduleId = '" +objTmp[1].toString()+"'"+
						"and typeId = '"+objTmp[0].toString()+"'" +
								"and isActivated = '1'";
				Query sqlQuSchedule = connMgr.getCurrentSession(TBLoc).createSQLQuery(sqlSchedule);
				List<Object[]> ob = (List<Object[]>) sqlQuSchedule.list();
				System.out.println(ob.get(0)[0].toString() +" "+ ob.get(0)[1].toString());
				SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
				
				Date date = new Date();
				try {
					date = simple.parse((ob.get(0)[0].toString()+" " +ob.get(0)[1].toString()));
					System.out.println(date.toString());
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					
				}
				
				if(date.after(currentTime.getTime()))
					System.out.println("date before");
				else {
					menuListView.setId(Long.parseLong(String.valueOf(objTmp[1])));
					menuListView.setSubtitle(String.valueOf(objTmp[4]));
					menuListView.setTitle(String.valueOf(objTmp[3]));
					menuListView.setTypeId(String.valueOf(objTmp[0]));
					PictureView pictureView = new PictureView();
					pictureView.setId(Long.parseLong(objTmp[2].toString()));
					pictureView.setVersion(Long.parseLong(objTmp[5].toString()));
					menuListView.setPictureView(pictureView);
					lisMenuListViews.add(menuListView);	
					System.out.println("date after scheduled");	
				}	
				
				
			}
		}
		
		
		return lisMenuListViews;
	}

	/**
	 * getMenuListViews method for MainMenuService (MASTER)
	 * @param orgId , propId , languageId 
	 * @return list of MenuListView 
	 */	
	@Override
	public List<MenuListView> getMasterMenuListViews(long orgId, long propId,String langId) {
		String sql = 
				"SELECT  ecms_hotel_staff_v03.global_module_info.typeId," +
				"ecms_hotel_staff_v03.global_module_info.id, " +
				"ecms_hotel_staff_v03.global_module_info.menuPictureRef," +
				"(select ecms_hotel_staff_v03.global_text.textUnicodeValue from ecms_hotel_staff_v03.global_text " +
					"where languageId = '"+langId+"' and id =ecms_hotel_staff_v03.global_module_info.titleRef) as 'Title'," +
				"(select ecms_hotel_staff_v03.global_text.textUnicodeValue from ecms_hotel_staff_v03.global_text " +
					"where languageId = '"+langId+"' and id =ecms_hotel_staff_v03.global_module_info.subTitleRef) as 'SubTitle'," +
				"(select ecms_hotel_staff_v03.global_picture.version from ecms_hotel_staff_v03.global_picture " +
					"where id =ecms_hotel_staff_v03.global_module_info.menuPictureRef) as 'menuPicturerefVersion'" +
					", ecms_hotel_staff_v03.mdl_main_screen.pictureFrameRef ,"+
					" global_module_info.isScheduleScreen  "+
					"FROM ecms_hotel_staff_v03.mdl_main_screen_menu_list " +
						" inner join ecms_hotel_staff_v03.mdl_main_screen on mdl_main_screen.id = ecms_hotel_staff_v03.mdl_main_screen_menu_list.mainScreenId" +
						" inner join ecms_hotel_staff_v03.global_module_info on ecms_hotel_staff_v03.mdl_main_screen_menu_list.moduleId = ecms_hotel_staff_v03.global_module_info.id" +
						" where ecms_hotel_staff_v03.mdl_main_screen.proprietaryId = "+propId+" and ecms_hotel_staff_v03.mdl_main_screen_menu_list.isActive = true and " +
								"ecms_hotel_staff_v03.mdl_main_screen.type =0 " +
						"order by  ecms_hotel_staff_v03.mdl_main_screen_menu_list.displayOrder";
		Query sqlQu = connMgr.getCurrentSession(TBLoc).createSQLQuery(sql);
//		sqlQu.setParameter("orgId", orgId);
//		sqlQu.setParameter("propId", orgId);
//		sqlQu.setParameter("languageID", langId);
		
		int id = 1 ;
		List<MenuListView> lisMenuListViews = new ArrayList<MenuListView>();
		List<Object[]> objects = (List<Object[]>)sqlQu.list();
		System.out.println(objects.size());
		for(Object[] objTmp : objects){
			MenuListView menuListView = new MenuListView();
			for(int i =0 ; i<objTmp.length ; i++){
				
				System.out.println(String.valueOf(objTmp[i]));
				
			}
			if(!Boolean.parseBoolean(objTmp[7].toString())){
				
			
			menuListView.setId(Long.parseLong(String.valueOf(objTmp[1])));
			menuListView.setSubtitle(String.valueOf(objTmp[4]));
			menuListView.setTitle(String.valueOf(objTmp[3]));
			menuListView.setTypeId(String.valueOf(objTmp[0]));
			PictureView pictureView = new PictureView();
			pictureView.setId(Long.parseLong(objTmp[2].toString()));
			pictureView.setVersion(Long.parseLong(objTmp[5].toString()));
			menuListView.setPictureView(pictureView);
			lisMenuListViews.add(menuListView);
			System.out.println("not scheduled");
			}		
			else {
				
				System.out.println("scheduled "+ objTmp[0].toString() + " " +objTmp[1].toString() );
				Calendar currentTime = Calendar.getInstance();
				System.out.println(currentTime.getTime());
				String sqlSchedule = "SELECT activationDate , activationTime , isActivated " +
						"FROM ecms_hotel_staff_v03.schedule_activation	" +
						"where ecms_hotel_staff_v03.schedule_activation.moduleId = '" +objTmp[1].toString()+"'"+
						"and typeId = '"+objTmp[0].toString()+"'" +
								"and isActivated = '1'";
				Query sqlQuSchedule = connMgr.getCurrentSession(TBLoc).createSQLQuery(sqlSchedule);
				List<Object[]> ob = (List<Object[]>) sqlQuSchedule.list();
				System.out.println(ob.get(0)[0].toString() +" "+ ob.get(0)[1].toString());
				SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
				
				Date date = new Date();
				try {
					date = simple.parse((ob.get(0)[0].toString()+" " +ob.get(0)[1].toString()));
					System.out.println(date.toString());
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					
				}
				
				if(date.after(currentTime.getTime()))
					System.out.println("date before");
				else {
					menuListView.setId(Long.parseLong(String.valueOf(objTmp[1])));
					menuListView.setSubtitle(String.valueOf(objTmp[4]));
					menuListView.setTitle(String.valueOf(objTmp[3]));
					menuListView.setTypeId(String.valueOf(objTmp[0]));
					PictureView pictureView = new PictureView();
					pictureView.setId(Long.parseLong(objTmp[2].toString()));
					pictureView.setVersion(Long.parseLong(objTmp[5].toString()));
					menuListView.setPictureView(pictureView);
					lisMenuListViews.add(menuListView);	
					System.out.println("date after scheduled");	
				}	
				
				
			}
		}
		
		
		return lisMenuListViews;
	}
/**
 * getPictureFrameViews method for MainMenuService
 * @param orgId , propId
 * @return pictureFrameView 
 */
	@Override
	public PictureFrameView getPictureFrameViews(long orgId, long propId , int type) {
		// TODO Auto-generated method stub
		System.out.println(propId+" get Frame");
		String sql = "SELECT * FROM ecms_hotel_staff_v03.picture_frame_v where proprietaryId ='"+propId+"' and frameId ="+
				" (select pictureFrameRef from ecms_hotel_staff_v03.mdl_main_screen where proprietaryId='"+propId+"' and type ="+type+")";
		Query sqlQu = connMgr.getCurrentSession(TBLoc).createSQLQuery(sql)
				.addScalar("frameId",Hibernate.INTEGER)
				.addScalar("frameVersion",Hibernate.INTEGER)
				.addScalar("period",Hibernate.INTEGER)
				.addScalar("proprietaryId",Hibernate.INTEGER)
				.addScalar("pictureId",Hibernate.INTEGER)
				.addScalar("pictureVersion",Hibernate.INTEGER)
				.addScalar("binaryData",Hibernate.BLOB);
		
//		sqlQu.setParameter("orgId", orgId);
//		sqlQu.setParameter("propId", propId);
//		sqlQu.setParameter("id", id);
		
//		PictureFrameView pictureFrameView = new PictureFrameView();
		List<Object[]> objects = (List<Object[]>)sqlQu.list();
		System.out.println(objects.size()+" ");
		PictureFrameView pictureFrameView = new PictureFrameView();
		pictureFrameView.setId(Long.parseLong(objects.get(0)[0].toString()));
		pictureFrameView.setPeriod(Long.parseLong(objects.get(0)[2].toString()));
		pictureFrameView.setVersion(Long.parseLong(objects.get(0)[1].toString()));
		List<PictureView> lisPictureViews = new ArrayList<PictureView>();
		for(Object[] objTmp : objects){
			PictureView pictureView = new PictureView();
			pictureView.setId(Long.parseLong(objTmp[4].toString()));
			pictureView.setVersion(Long.parseLong(objTmp[5].toString()));
			
		lisPictureViews.add(pictureView);	
		}
		pictureFrameView.setPictureViewList(lisPictureViews);
		
	
		return pictureFrameView;	}


}
